﻿## =====================================================================
## Title       : Create a Database Mirror
## Description : Given a database, a primary instance, and a secondary
##				 instance, this will backup, restore and initialize a mirror
##				 of the given database. Requires a share accessible by both
##				 servers and mirroring endpoints. 
## Author      : Ed Snajder
## Date        : 2/5/2011
## Input       : The name of the DB to be mirrored
## 				 Primary Server\Instance
## 				 Secondary Server\Instance
## Output      :  N\A
## Usage	   :  Used for automating mirror tasks
## Notes	   :  "Snajder" is Polish for "You would not be wise running this in production."
## Tag		   :  sql_server, database_mirroring, mssql
## Change log  :
## =====================================================================
param
(
	[string]$dbToMirror="Adventureworks2012",	#name of db you want to mirror
	[string]$destPrincipal=".\SQL2012A",					#instance where DB is now. Will be the primary
	[string]$destMirror=".\SQL2012B",						#instance where you are going to mirror to
	[string]$witnessEnpoint = $null,							#Witness Endpoint $null for no Witness Server
	[string]$fixRecovery=1												#flag to change recovery from simple to full if it is simple
)
#Load SMO assemblies
	[void][reflection.assembly]::LoadWithPartialName( "Microsoft.SqlServer.ConnectionInfo" );
	[void][reflection.assembly]::LoadWithPartialName( "Microsoft.SqlServer.SmoEnum" );
	[void][reflection.assembly]::LoadWithPartialName( "Microsoft.SqlServer.Smo" );
	[void][reflection.assembly]::LoadWithPartialName( "Microsoft.SqlServer.SmoExtended" );
#Write Errors to Log	
Trap [Exception] {
        $err = $_.Exception
        while ( $err.InnerException )
            {
            $err = $err.InnerException
            $errorfullmessage += $err.Message + " "
            }; 
        $errorfullmessage | out-File ($dbBackupTempDir + "\MirroredDBBuildError_" + $templateDB + "_" `
            + $timeStamp + ".txt");
			Write-Host "[ERROR]" -ForegroundColor Red
			"An error occurred. Please see the log file in $dbBackupTempDir for more information."
			break;
    }
#Reusable Email function
function sendemail 
	{
	Param([String]$emailbody);
	$SQLMail = New-Object System.Net.Mail.MailMessage;
	$SQLMail.From = "SQLBot@localhost";
	$SQLMail.To.Add($requestEmailer);
	$SQLMail.CC.Add($dbaEmail);
	$SQLMail.IsBodyHTML = 1;
	$SQLMail.Subject = "Mirror Creation Complete - " + $(Get-Date);
	$SQLMail.Body = $emailbody;
	$smtp = New-Object System.Net.Mail.SmtpClient($smtpServer);
	$smtp.Send($SQLMail); 
	}
#Variables that are typically static to the template and instances being managed.
$dbBackupTempDir = "\\NT7A\C$\Temp";
#Share that is accessible by both mirrors
$dbBackupDir = "\\NT7A\share";
#FQDN of SQL Endpoints. Include the witness endpoint too
$parnter01Enpoint = 'TCP://NT7A:5022';
$parnter02Enpoint = 'TCP://NT7A:5023';
#Email notification body of message
$emailbody = "Mirror has been created for database $dbToMirror on Instance $destMirror"
#Primary Server Physical DB File Locations
$PrincipalPrimaryDir = "C:\MSSQL\MSSQL11.SQL2012A\MSSQL\DATA\";
$PrincipalLogDir = "C:\MSSQL\MSSQL11.SQL2012A\MSSQL\DATA\";
$PrincipalPrimaryFullPath = $PrincipalDataDir +  $dbToMirror + ".MDF";
$PrincipalLogFullPath = $PrincipalLogDir +  $dbToMirror + ".LDF";
#Mirror Server Physical DB File Locations
$MirrorPrimaryDir = "C:\MSSQL\MSSQL11.SQL2012B\MSSQL\DATA\";
$MirrorLogDir = "C:\MSSQL\MSSQL11.SQL2012B\MSSQL\DATA\";
$MirrorPrimaryFullPath = $MirrorDataDir +   $dbToMirror + ".MDF";
$MirrorLogFullPath = $MirrorLogDir +  $dbToMirror + ".LDF";
##################################################################
#No editing below here, unless you are fixing something
##################################################################
Write-Host "[PROCESS START]" -ForegroundColor Blue
"Clone database $dbToMirror and establish mirroring between principal $destPrincipal and mirror $destMirror.";
#Timestamp for logs, backup files
$timeStamp = Get-Date -Format yyyyMMddHHmmss;
##Database Connections, Principal and Mirror
$smoPrincipal = New-Object('Microsoft.SqlServer.Management.Smo.Server') ($destPrincipal);
$smoMirror = New-Object('Microsoft.SqlServer.Management.Smo.Server') ($destMirror);
$newBackupDir = $dbBackupDir  
$dbMirrorSource=$smoPrincipal.Databases[$dbToMirror]
#Check Recovery Model of the Database
if ($dbMirrorSource.RecoveryModel -ne "Full")
{ 
#If parameter set to fix recovery, this will change a simple recovery db to full
	if ($fixRecovery -eq 1)
	{
	Write-Host "[INFO]" -ForegroundColor Green
	"$dbToMirror in Simple Recovery, converting to Full Recovery for mirroring..."
	$dbMirrorSource.RecoveryModel = "Full"
	$dbMirrorSource.alter()
	$dbMirrorSource.Refresh();
	$smoPrincipal.Refresh();
	Write-Host "[INFO]" -ForegroundColor Green
	"Full Recovery Set. Be sure to add log backups to your maintenance plan!"
	}
	else
	{
	# If DB is simple recovery, and parameter not set to change it, error out
	Write-Host "[ERROR]" -ForegroundColor Red
	"Database must be in full recovery for mirroring, or parameter fixRecovery must be set to 1"
	break;
	}
}
#Take Full Backup from Principal and Save to shared volume
$newDBBackupName = $newBackupDir + "\" + $dbToMirror + "_FULL4MIRROR_" + $timeStamp + ".BAK"
$newDBBackup = New-Object('Microsoft.SqlServer.Management.Smo.Backup')
$newDBBackup.Action = "Database";
$newDBBackup.BackupSetDescription = "Backup of $dbToMirror for mirror creation"
$newDBBackup.Database = $dbToMirror;
$newDBBackup.MediaDescription = "Disk";
$newDBBackup.Devices.AddDevice($newDBBackupName, "File");
$newDBBackup.Initialize=$true;
if (Test-Path $newBackupDir)
	{
	Write-Host "[INFO]" -ForegroundColor Green
	"Making Backup of $dbToMirror..."
	$newDBBackup.SqlBackup($smoPrincipal);
	}
else {
    Write-Host "[ERROR]" -ForegroundColor Red
	"Backup Directory not found. Check your configuration."
	break;
	}
Write-Host "[INFO]" -ForegroundColor Green
"Backup Created as $newDBBackupName on $newBackupDir. Creating initial log backup for mirroring.";
#Make Transaction Log backup of Primary Server
$newDBLogBackupName = $newBackupDir + "\" + $dbToMirror + "_TLOG4MIRROR_" + $timeStamp + ".TRN"
$newDBLogBackup = New-Object('Microsoft.SqlServer.Management.Smo.Backup')
$newDBLogBackup.Action = "Log";
$newDBLogBackup.BackupSetDescription = "Initial Transaction Log Backup of " + $dbToMirror;
$newDBLogBackup.Database = $dbToMirror;
$newDBLogBackup.MediaDescription = "Disk";
$newDBLogBackup.Devices.AddDevice($newDBLogBackupName, "File");
$newDBLogBackup.Initialize=$true;
$newDBLogBackup.SqlBackup($smoPrincipal);
Write-Host "[INFO]" -ForegroundColor Green
"Restoring backups to mirror instance on $destMirror"
#Restore Backup to Mirror
#Establish mirror instance connection
$smoMirrorDB = New-Object ('Microsoft.Sqlserver.Management.Smo.Restore') 
$smoMirrorDB.Devices.AddDevice($newDBBackupName, "File");
$smoMirrorDB.NoRecovery = $true;
$smoMirrorDB.ReplaceDatabase = $false;
$smoMirrorDB.Action = "Database";
$smoMirrorDB.PercentCompleteNotification = 50;
$smoMirrorDB.Database =  $dbToMirror;

#Retrieve data and log file information from the backup file...
$dataFileGroups = $dbMirrorSource.FileGroups
$dataLogs = $dbMirrorSource.LogFiles
foreach ($dbDataFiles in $dataFileGroups)
	{
	foreach ($dbfile IN $dbDataFiles.Files)
		{
		$smoRestoreMirror  = New-Object ('Microsoft.SqlServer.Management.Smo.RelocateFile');
		$smoRestoreMirror.LogicalFileName = $dbfile.Name;
		$smoRestoreMirror.PhysicalFileName = $dbfile.FileName.Replace($PrincipalPrimaryDir,$MirrorPrimaryDir);
		$smoMirrorDB.RelocateFiles.Add($smoRestoreMirror);
		}
	}
foreach ($dblog IN $dataLogs)
	{
	$smoRestoreMirror  = New-Object ('Microsoft.SqlServer.Management.Smo.RelocateFile');
	$smoRestoreMirror.LogicalFileName = $dblog.Name;
	$smoRestoreMirror.PhysicalFileName = $dblog.FileName.Replace($PrincipalLogDir,$MirrorLogDir);
	$smoMirrorDB.RelocateFiles.Add($smoRestoreMirror);
	}
#Restore Files
$smoMirrorDB.SqlRestore($smoMirror);
$smoMirrorDBLog = New-Object ('Microsoft.Sqlserver.Management.Smo.Restore') 
$smoMirrorDBLog.Devices.AddDevice($newDBLogBackupName, "File");
$smoMirrorDBLog.NoRecovery = $true;
$smoMirrorDBLog.Action = "Log";
$smoMirrorDBLog.Database =  $dbToMirror;
$smoMirrorDBLog.PercentCompleteNotification = 20;
$smoMirrorDBLog.SqlRestore($smoMirror);
#Connect Mirror to Principal
$newMirrorDB = $smoMirror.Databases["master"];
$mirrorQuery = "ALTER DATABASE " + $dbToMirror + " SET PARTNER = '" + $parnter01Enpoint + "'"
$newMirrorDB.ExecuteNonQuery($mirrorQuery);
Write-Host "[INFO]" -ForegroundColor Green
"Mirror to principal connection complete"
#Connect Principal to Mirror
$newPrincipalDB = $smoPrincipal.Databases[$dbToMirror];
$principalQuery = "ALTER DATABASE " + $dbToMirror + " SET PARTNER = '" + $parnter02Enpoint + "'"
$newPrincipalDB.ExecuteNonQuery($principalQuery)
Write-Host "[INFO]" -ForegroundColor Green
"Principal to mirror connection complete."
#Connect Witness if Provided
if ($witnessEnpoint) {
	$witnessQuery = "ALTER DATABASE " + $dbToMirror + " SET WITNESS = '" + $witnessEnpoint + "'"
	$newPrincipalDB.ExecuteNonQuery($witnessQuery)
	Write-Host "[INFO]" -ForegroundColor Green
	"Principal to witness connection complete, all systems online"
} else {
	Write-Host "[INFO]" -ForegroundColor Green
	"No witness set, mirroring setup complete."
	}
	Write-Host "[INFO]" -ForegroundColor Green
	"Checking Synchronization Status..."
#Check Database Mirroring Status...if everything worked so far it should be Synchronized or Synchronizing
	$dbMirrorSource.Refresh()
	while ($dbMirrorSource.MirroringStatus -ne "Synchronized")
		{
		switch ($dbMirrorSource.MirroringStatus)
		{	"Synchronizing" {
			#If synchronizing, refresh until it changes its status to "Sychronized. Time to sycnrhonize will vary.
					$dbMirrorSource.Refresh()
					$dbMirrorSource.MirroringStatus	}
			#If anything but Synchronized\Sychronizing, break out of script, something did not work
			"Suspended"	{
					Write-Host "[ERROR] DB Mirror Check Failed - Showing Status of Suspended"
					"Please investigate the SQL Server Error Logs for reasons behind suspension."
					break;
					}
			"Disconnected"	{
					Write-Host "[ERROR] DB Mirror Check Failed - Showing Status of Disconnected"
					"Please investigate the SQL Server Error Logs for reasons behind disconnect."
					break;
					}
			"None"	{
					Write-Host "[ERROR] DB Mirror Check Failed - Mirroring not enabled"
					"The mirror was not successfully built. Check secondary server and try again."
					break;
					}
			"PendingFailover"    {
					Write-Host "[ERROR] DB Mirror Check Failed - Mirroring not enabled"
					"The mirror is failing over, even though no instruction was given. Investigate instance health."
					break;
					}
				}
				}
"...Synchronization Complete!"
$mirrorStatus = $dbMirrorSource.MirroringStatus;
Write-Host "DB Mirror Status: $mirrorStatus " -ForegroundColor Darkgreen
Write-Host "[PROCESS COMPLETE]" -ForegroundColor Darkgreen
	
